import pymysql
from bidspider.settings import MY_SQL
from utils.text_utils import TextUtils
from bidspider.items import BidspiderItem


class MySqlDba:
    dba = None
    conn = None

    @classmethod
    def get_instance(cls):
        if cls.dba is None:
            cls.dba = MySqlDba()
            cls.conn = pymysql.connect(
                host=MY_SQL['host'],
                port=MY_SQL['port'],
                user=MY_SQL['user'],
                password=MY_SQL['password'],
                database=MY_SQL['database']
            )
        return cls.dba

    def close_conn(self):
        if self.conn:
            self.conn.close()

    def save_bid(self, item):
        # 查询标题是否已存在
        if self.title_exist(item['title']):
            return False
        # 存储对象
        cursor = None
        try:
            cursor = self.conn.cursor()
            # 存储标题
            sql = "INSERT INTO bid_title (title) values (%s)"
            cursor.execute(sql, item['title'])
            # 存储对象
            sql = ("INSERT INTO bid (id, title, ctime, btype, bcode, amount, endtime, city, cityname, source, "
                   "link, labels, abstracts, category, content, master, experts, project_contact, project_phone, "
                   "master_address, master_contact, proxy_name, proxy_address, proxy_contact, bidder) "
                   "values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, "
                   "%s, %s, %s)")
            cursor.execute(sql, (TextUtils.get_uuid(), item['title'], item['ctime'], TextUtils.get(item, 'btype'),
                                 TextUtils.get(item, 'bcode'), TextUtils.get(item, 'amount'),
                                 TextUtils.get(item, 'endtime'), TextUtils.get(item, 'city'),
                                 TextUtils.get(item, 'cityname'), TextUtils.get(item, 'source'),
                                 TextUtils.get(item, 'link'), TextUtils.get(item, 'labels'),
                                 TextUtils.get(item, 'abstracts'), TextUtils.get(item, 'category'),
                                 TextUtils.get(item, 'content'), TextUtils.get(item, 'master'),
                                 TextUtils.get(item, 'experts'), TextUtils.get(item, 'project_contact'),
                                 TextUtils.get(item, 'project_phone'), TextUtils.get(item, 'master_address'),
                                 TextUtils.get(item, 'master_contact'), TextUtils.get(item, 'proxy_name'),
                                 TextUtils.get(item, 'proxy_address'), TextUtils.get(item, 'proxy_contact'),
                                 TextUtils.get(item, 'bidder')))
            self.conn.commit()
        except Exception as e:
            self.conn.rollback()
            print("保存错误：", str(e))
            return False
        finally:
            if cursor:
                cursor.close()
        return True

    def delete_bid(self, item):
        if not ('id' in item and item['id']):
            return False
        cursor = self.conn.cursor()
        sql = "DELETE FROM bid WHERE id=%s"
        cursor.execute(sql, item['id'])
        self.conn.commit()
        return True

    def update_bid(self, item):
        if not ('id' in item and item['id']):
            return False
        cursor = self.conn.cursor()
        sql = ("UPDATE bid SET title=%s, ctime=%s, btype=%s, bcode=%s, amount=%s, endtime=%s, city=%s, "
               "cityname=%s, source=%s, link=%s, labels=%s, abstracts=%s, category=%s, content=%s, "
               "master=%s, experts=%s, project_contact=%s, project_phone=%s, master_address=%s, "
               "master_contact=%s, proxy_name=%s, proxy_address=%s, proxy_contact=%s, bidder=%s "
               "WHERE id=%s")
        cursor.execute(sql, (item['title'], item['ctime'], TextUtils.get(item, 'btype'),
                             TextUtils.get(item, 'bcode'), TextUtils.get(item, 'amount'),
                             TextUtils.get(item, 'endtime'), TextUtils.get(item, 'city'),
                             TextUtils.get(item, 'cityname'), TextUtils.get(item, 'source'),
                             TextUtils.get(item, 'link'), TextUtils.get(item, 'labels'),
                             TextUtils.get(item, 'abstracts'), TextUtils.get(item, 'category'),
                             TextUtils.get(item, 'content'), TextUtils.get(item, 'master'),
                             TextUtils.get(item, 'experts'), TextUtils.get(item, 'project_contact'),
                             TextUtils.get(item, 'project_phone'), TextUtils.get(item, 'master_address'),
                             TextUtils.get(item, 'master_contact'), TextUtils.get(item, 'proxy_name'),
                             TextUtils.get(item, 'proxy_address'), TextUtils.get(item, 'proxy_contact'),
                             TextUtils.get(item, 'bidder'), item['id']))
        self.conn.commit()
        return True

    def get_bid_list(self, state='0', size=10):
        cursor = self.conn.cursor()
        sql = ('SELECT `id`,`title`,`ctime`,`btype`,`bcode`,`amount`,`endtime`,`city`,`cityname`,`source`,`link`,'
               '`labels`,`abstracts`,`category`,`content`,`master`,`experts`,`project_contact`,`project_phone`,'
               '`master_address`,`master_contact`,`proxy_name`,`proxy_address`,`proxy_contact`,`bidder`,`state` '
               'FROM bid WHERE state=%s LIMIT %s')
        cursor.execute(sql, (state, size))
        result = cursor.fetchall()
        items = []
        for row in result:
            item = BidspiderItem()
            item['id'] = row[0]
            item['title'] = row[1]
            item['ctime'] = row[2]
            item['btype'] = row[3]
            item['bcode'] = row[4]
            item['amount'] = row[5]
            item['endtime'] = row[6]
            item['city'] = row[7]
            item['cityname'] = row[8]
            item['source'] = row[9]
            item['link'] = row[10]
            item['labels'] = row[11]
            item['abstracts'] = row[12]
            item['category'] = row[13]
            item['content'] = row[14]
            item['master'] = row[15]
            item['experts'] = row[16]
            item['project_contact'] = row[17]
            item['project_phone'] = row[18]
            item['master_address'] = row[19]
            item['master_contact'] = row[20]
            item['proxy_name'] = row[21]
            item['proxy_address'] = row[22]
            item['proxy_contact'] = row[23]
            item['bidder'] = row[24]
            item['state'] = row[25]
            items.append(item)
        cursor.close()
        return items

    def save_title(self, title):
        cursor = None
        try:
            cursor = self.conn.cursor()
            sql = "INSERT INTO bid_title (title) values (%s)"
            cursor.execute(sql, title)
            self.conn.commit()
        except:
            self.conn.rollback()
        finally:
            if cursor:
                cursor.close()
        return True

    def title_exist(self, title):
        cursor = self.conn.cursor()
        sql = 'SELECT COUNT(*) FROM bid_title WHERE title=%s'
        cursor.execute(sql, title)
        result = cursor.fetchone()
        cursor.close()
        return result[0] > 0
